create database dim;
use dim;
-- dim_customer_i
CREATE EXTERNAL TABLE if not exists dim_customer_i(
    id                       INT                    COMMENT '主键ID',
    customer_relationship_id INT                    COMMENT '当前意向ID',
    create_date_time         string                 COMMENT '创建时间',
    update_date_time         string                 COMMENT '最后更新时间',
    deleted                  string                 COMMENT '是否被删除（禁用）',
    name                     STRING                 COMMENT '姓名',
    idcard                   STRING                 COMMENT '身份证号',
    birth_year               INT                    COMMENT '出生年份',
    gender                   STRING                 COMMENT '性别',
    phone                    STRING                 COMMENT '手机号',
    wechat                   STRING                 COMMENT '微信',
    qq                       STRING                 COMMENT 'QQ号',
    email                    STRING                 COMMENT '邮箱',
    area                     STRING                 COMMENT '所在区域',
    leave_school_date        string                   COMMENT '离校时间',
    graduation_date          string                   COMMENT '毕业时间',
    bxg_student_id           STRING                 COMMENT '博学谷学员ID，可能未关联到，不存在',
    creator                  INT                    COMMENT '创建人ID',
    origin_type              STRING                 COMMENT '数据来源',
    origin_channel           STRING                 COMMENT '来源渠道',
    tenant                   INT                    COMMENT '租户',
    md_id                    INT                    COMMENT '中台ID'
)
COMMENT '客户静态信息表'
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS orc
tblproperties("orc.compress"="SNAPPY");

CREATE EXTERNAL TABLE IF NOT EXISTS dim_itcast_subject_i(
    id INT,
    create_date_time string COMMENT '创建时间',
    update_date_time string COMMENT '最后更新时间',
    deleted string COMMENT '是否被删除（禁用）',
    name STRING COMMENT '学科名称',
    code STRING COMMENT '学科代码',
    tenant INT COMMENT '租户'
)
COMMENT '学科表'
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');

CREATE EXTERNAL TABLE dim_itcast_school_i(
    id INT,
    create_date_time string COMMENT '创建时间',
    update_date_time string COMMENT '最后更新时间',
    deleted string COMMENT '是否被删除（禁用）',
    name STRING COMMENT '校区名称',
    code STRING ,
    tenant INT
)
COMMENT '校区表'
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');

create EXTERNAL table dim_employee_i(
    id INT,
    email STRING COMMENT '公司邮箱，OA登录账号',
    real_name STRING COMMENT '员工的真实姓名',
    phone STRING COMMENT '手机号，目前还没有使用；隐私问题OA接口没有提供这个属性，',
    department_id STRING COMMENT 'OA中的部门编号，有负值',
    department_name STRING COMMENT 'OA中的部门名',
    remote_login string COMMENT '员工是否可以远程登录',
    job_number STRING COMMENT '员工工号',
    cross_school string COMMENT '是否有跨校区权限',
    last_login_date string COMMENT '最后登录日期',
    creator INT COMMENT '创建人',
    create_date_time string COMMENT '创建时间',
    update_date_time string COMMENT '最后更新时间',
    deleted string COMMENT '是否被删除（禁用）',
    scrm_department_id INT COMMENT 'SCRM内部部门id',
    leave_office string COMMENT '离职状态',
    leave_office_time string COMMENT '离职时间',
    reinstated_time string COMMENT '复职时间',
    superior_leaders_id INT COMMENT '上级领导ID',
    tdepart_id INT COMMENT '直属部门',
    tenant INT,
    ems_user_name STRING
)
COMMENT '员工表'
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');


CREATE EXTERNAL TABLE dim_department_i (
    id INT COMMENT '部门id',
    name STRING COMMENT '部门名称',
    parent_id INT COMMENT '父部门id',
    create_date_time string COMMENT '创建时间',
    update_date_time string COMMENT '更新时间',
    deleted string COMMENT '删除标志',
    id_path STRING COMMENT '编码全路径',
    tdepart_code INT COMMENT '直属部门',
    creator STRING COMMENT '创建者',
    depart_level INT COMMENT '部门层级',
    depart_sign INT COMMENT '部门标志，暂时默认1',
    depart_line INT COMMENT '业务线，存储业务线编码',
    depart_sort INT COMMENT '排序字段',
    disable_flag INT COMMENT '禁用标志',
    tenant INT
)
COMMENT '部门表'
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');

CREATE TABLE dim_employee_department_i (
    id INT comment '员工ID',
    tdepart_id INT COMMENT '直属部门',
    name STRING COMMENT '部门名称'
)
COMMENT '员工部门表'
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');

insert overwrite table dim_employee_department_i partition(dt)
select e.id,e.tdepart_id,d.name,e.dt
from dim_employee_i e join dim_department_i d on e.tdepart_id = d.id


